﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.OleDb;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Views.Grid;
using ERP_RORZE_DLL.Product;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.DAO;
using System.Data.SqlClient;
using ERP_RORZE_DLL;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.BusOder;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_DLL.Product.Base;
using BaseDAO = ERP_RORZE_TOOL.DBAccess.BaseDAO;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.MO
{
    public partial class ctrlMOList : DevExpress.XtraEditors.XtraUserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        DXPopupMenu menu;
        private LogUtils log;
        private int range = 1;
        //
        ClsMODAO clsMODAO = new ClsMODAO();
        DataTable dt;
        ClsMOTable b = new ClsMOTable();

        public ctrlMOList()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);
            log = new LogUtils();

            InitDateRangeMenu();
            this.dropbtDateRange.DropDownControl = menu;
            this.dropbtDateRange.Text = "3 Months";
        }
        void Reload(object sender, EventArgs e)
        {
            BindDatatoGrid();
        }
        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có thực sự muốn xóa dữ liệu!", "Message", MessageBoxButtons.OKCancel,
                                MessageBoxIcon.Question) == DialogResult.OK)
            {
                b.MONo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo").ToString();
                clsMODAO.DeleteMO(b);

                //write Log
                log.Table = LogConstant.Table.Product.MOList;
                log.Task = LogConstant.Task.Delete;
                log.Description = "MONo: " + b.MONo;
                log.WriteLog();

            }
            Reload(sender, e);
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            frm_MOAdd frmAdd = new frm_MOAdd();
            frmAdd.Reload += new EventHandler(Reload);
            frmAdd.ShowDialog();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            try
            {
                frm_MOEdit frmEdit = new frm_MOEdit();
                frmEdit.Reload += new EventHandler(Reload);
                frmEdit.BlastQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "BlastQty").ToString();
                frmEdit.date = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "date").ToString();
                frmEdit.DeadLine = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "DeadLine").ToString();
                frmEdit.Finishdate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Finishdate").ToString();
                frmEdit.Finished = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Finished").ToString();
                frmEdit.HelisertQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "HelisertQty").ToString();
                frmEdit.Imported = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Imported").ToString();
                frmEdit.MONo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo").ToString();
                frmEdit.MOQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MOQty").ToString();
                frmEdit.NGQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "NGQty").ToString();
                frmEdit.Note = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Note").ToString();
                frmEdit.OKQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "OKQty").ToString();
                frmEdit.PartID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
                frmEdit.startdate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "startdate").ToString();
                frmEdit.Started = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Started").ToString();
                frmEdit.Show();
            }
            catch
            { }
        }

        private void ctrlMOList_Load(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            //DataSet as400 = new DataSet();
            //DataSet staff = new DataSet();
            //string sql = null;
            //SqlCommand cmd;
            //SqlDataAdapter adapter;
            //DataTable staff_db2;
            //DataTable staff_local;                
            //try
            //{
            //    //Get Data From AS400                
            //    masterConnection.Open();
            //    sql = "select * from openquery(AS400_MPFLIB, 'Select MPPR00.PRWORK,MPOR00.ORODNO,substring(MPOR00.ORHNBN,6) AS ORHNBN,MPOR00.ORHTSU,SubString(MPOR00.ORFILL,50,7) AS ORFILL from MPOR00 Left Join MPPR00 On MPOR00.ORHNBN=MPPR00.PRHNBN Where MPOR00.ORHNBN Like ''CSZ0%'' And MPPR00.PRWORK=''W241''')";
            //    cmd = new SqlCommand(sql, masterConnection);
            //    adapter = new SqlDataAdapter();
            //    adapter.SelectCommand = cmd;
            //    adapter.Fill(as400);
            //    staff_db2 = as400.Tables[0];
            //    adapter.Dispose();
            //    cmd.Dispose();
            //    masterConnection.Close(); 

            //    //Get Data From Local Database
            //    myConnection.Open();
            //    sql = "SELECT * FROM [242_MO]";
            //    cmd = new SqlCommand(sql, myConnection);
            //    adapter = new SqlDataAdapter();
            //    adapter.SelectCommand = cmd;
            //    adapter.Fill(staff, "242_MO");
            //    staff_local = staff.Tables["242_MO"];

            //    foreach (DataRow as400_row in staff_db2.Rows)
            //    {
            //        DataRow[] current_row = staff_local.Select("MONo='" + as400_row["ORODNO"].ToString() + "'");
            //        if (current_row.Length > 0)
            //        {
            //            foreach (DataRow local_row in current_row)
            //            {
            //                local_row.BeginEdit();
            //                local_row["PartID"] = as400_row["ORHNBN"].ToString();
            //                local_row["MOQty"] = as400_row["ORHTSU"].ToString();
            //                //local_row["BOrderNo"] = as400_row["ORFILL"].ToString();                            
            //                local_row.EndEdit();
            //            }
            //        }
            //        else
            //        {
            //            DataRow new_row = staff_local.NewRow();
            //            new_row["MONo"] = as400_row["ORODNO"].ToString();
            //            new_row["PartID"] = as400_row["ORHNBN"].ToString();
            //            new_row["MOQty"] = as400_row["ORHTSU"].ToString();
            //            //new_row["BOrderNo"] = as400_row["ORFILL"].ToString(); 
            //            new_row["Started"] = false;
            //            new_row["Finished"] = false;

            //            staff_local.Rows.Add(new_row);
            //        }
            //    }  


            //    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
            //    //Update to Local Database
            //    adapter.Update(staff, "242_MO");

            //    adapter.Dispose();
            //    cmd.Dispose();
            //    myConnection.Close();

            //    //Update History
            //    myConnection.Open();
            //    sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=4";
            //    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
            //    cmd_sql.ExecuteNonQuery();
            //    cmd_sql.Dispose();
            //    myConnection.Close();

            //    Reload(sender,e);

            //    MessageBox.Show("Database has been updated successfull.");

            //}
            //catch (Exception ex)
            //{
            //    if (masterConnection.State == ConnectionState.Open)
            //    {
            //        masterConnection.Close();
            //    }

            //    if (myConnection.State == ConnectionState.Open)
            //    {
            //        myConnection.Close();
            //    }
            //    MessageBox.Show(ex.Message);
            //    //MessageBox.Show("Cannot connect to AS400");
            //}
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnImport_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            btnEdit_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }

        private void btnBusOderCancel_Click(object sender, EventArgs e)
        {

            frm_MOFinish frmMOFinish = new frm_MOFinish();
            frmMOFinish.Text = "MO Finish";
            frmMOFinish.Reload += new EventHandler(Reload);
            frmMOFinish.Show();
        }

        private void btnNOCheckInput_Click(object sender, EventArgs e)
        {
            frm_MONGCheckInput frmNGCheck = new frm_MONGCheckInput();
            frmNGCheck.Text = "MO Not Good Check Input";
            frmNGCheck.Reload += new EventHandler(Reload);
            frmNGCheck.Show();
        }

        private void btnMONoUpdate_Click(object sender, EventArgs e)
        {
            frm_MONoUpdate frmMONoUpdate = new frm_MONoUpdate();
            frmMONoUpdate.Text = "MONo Update";
            frmMONoUpdate.Reload += new EventHandler(Reload);
            frmMONoUpdate.Show();
        }

        private void btnMOQtyChange_Click(object sender, EventArgs e)
        {
            frm_MOQtyChange frmMOQtyChange = new frm_MOQtyChange();
            frmMOQtyChange.Text = "MO Q'ty Change";
            frmMOQtyChange.Reload += new EventHandler(Reload);
            frmMOQtyChange.Show();
        }

        private void btnMOStart_Click(object sender, EventArgs e)
        {
            ctrlMOStart frmMOStart = new ctrlMOStart();
            frmMOStart.Text = "MO Start";
            frmMOStart.Reload += new EventHandler(Reload);
            frmMOStart.Show();
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }


        //update Import = 1 (Code test)
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();

            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("select [Date],[MONo],[PartNo],[Qty] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();


                    int line = 1;

                    myConnection.Open();

                    while (dReader.Read())
                    {
                        line++;

                        try
                        {
                            string sql = "UPDATE [242_MO] SET [Imported] = '1' WHERE [MONo] ='" +
                                         dReader["MONo"].ToString() +
                                         "'";
                            if (BaseDAO.ExecuteNonQuery(sql) > 0)
                            {
                                listDup.Add(new Utilities.ListItem("Update: ", dReader["MONo"].ToString(), "OK"));

                            }
                            else
                            {
                                listErr.Add(new Utilities.ListItem("Update: ", dReader["MONo"].ToString(), "Failed"));
                            }

                        }
                        catch (Exception ex)
                        {
                            listErr.Add(new Utilities.ListItem("Update: ", dReader["MONo"].ToString(), ex.Message));
                        }


                    }
                    myConnection.Close();
                    excelConnection.Close();
                    //MessageBox.Show(countOk + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")");
                    //LoadData();
                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "MONo");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }


            }
        }

        //Date range
        protected virtual void InitDateRangeMenu()
        {
            menu = new DXPopupMenu();
            menu.Items.Add(new DXMenuItem("3 Months", new EventHandler(OnItemClick3Month), null, null, null, null));
            menu.Items.Add(new DXMenuItem("6 Months", new EventHandler(OnItemClick6Months), null, null, null, null));
            menu.Items.Add(new DXMenuItem("One Year", new EventHandler(OnItemClick1Year), null, null, null, null));
            menu.Items.Add(new DXMenuItem("All time", new EventHandler(OnItemClickAllTime), null, null, null, null));

        }

        void OnItemClick3Month(object sender, EventArgs e)
        {
            BindDatatoGrid(1);
            range = 1;
            dropbtDateRange.Text = "3 Months";
        }

        void OnItemClick6Months(object sender, EventArgs e)
        {
            BindDatatoGrid(2);
            range = 2;
            dropbtDateRange.Text = "6 Months";
        }

        void OnItemClick1Year(object sender, EventArgs e)
        {
            BindDatatoGrid(3);
            range = 3;
            dropbtDateRange.Text = "One Year";
        }

        void OnItemClickAllTime(object sender, EventArgs e)
        {
            BindDatatoGrid(0);
            range = 0;
            dropbtDateRange.Text = "All time";
        }

        private void BindDatatoGrid(int range = 1)
        {
            Cursor.Current = Cursors.WaitCursor;
            dt = clsMODAO.GetAllMORange(range);
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();

            Cursor.Current = Cursors.Default;

        }

        private void gridView1_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
        {
            GridView view = sender as GridView;

            //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
            if (view.IsNewItemRow(e.RowHandle))
            {
                //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

            }
            //Cũ thì update
            else
            {
                string temDate = (view.GetRowCellValue(e.RowHandle, view.Columns["Finishdate"]).ToString().Trim() != string.Empty) ? DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["Finishdate"]).ToString()).ToString("yyyy/MM/dd") : view.GetRowCellValue(e.RowHandle, view.Columns["Finishdate"]).ToString().Trim();

                string Finishdate = (temDate == "0001/01/01" || temDate == "") ? "null" : "'" + DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["Finishdate"]).ToString()).ToString("yyyy/MM/dd") + "'";

                string Finished = (view.GetRowCellValue(e.RowHandle, view.Columns["Finished"]).ToString() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["Finished"]).ToString() + "'";
                string OKQty = (view.GetRowCellValue(e.RowHandle, view.Columns["OKQty"]).ToString() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["OKQty"]).ToString();
                string NGQty = (view.GetRowCellValue(e.RowHandle, view.Columns["NGQty"]).ToString() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["NGQty"]).ToString();
                string PartID = (view.GetRowCellValue(e.RowHandle, view.Columns["PartID"]).ToString() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["PartID"]).ToString() + "'";
                string MONo = (view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString() + "'";
                string HelisertQty = (view.GetRowCellValue(e.RowHandle, view.Columns["HelisertQty"]).ToString() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["HelisertQty"]).ToString();
                string BlastQty = (view.GetRowCellValue(e.RowHandle, view.Columns["BlastQty"]).ToString() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["BlastQty"]).ToString();

                string updateMo = "update [242_MO] set [Finished]=" + Finished
                    + ", [Finishdate] = " + Finishdate
                    + ", [OKQty] = " + OKQty+ ", [NGQty] = " + NGQty
                    + ", [PartID] = " + PartID
                    + ", [HelisertQty] = " + HelisertQty
                    + ", [BlastQty] = " + BlastQty
                    + " where [MONo] = " + MONo;

                string updateBusOrder = "update [242_BusOder] set [Finished]=" + Finished
                    + ", [Finishdate] = " + Finishdate
                    + ", [PartID] = " + PartID
                    + ", [BlastQty] = " + BlastQty
                    + ", [HelisertQty] = " + HelisertQty
                    + " where [MONo] = " + MONo;

                BaseDAO.ExecuteNonQuery(updateMo);

                //write Log
                log.Table = LogConstant.Table.Product.MOList;
                log.Task = LogConstant.Task.Update;
                log.Description = "Finished: " + Finished + "; Finishdate: " + Finishdate + "; OKQty: " + OKQty + "; PartID: " + PartID + " where MONo: " + MONo;
                log.WriteLog();

                BaseDAO.ExecuteNonQuery(updateBusOrder);

                //write Log
                log.Table = LogConstant.Table.Product.BusOrderList;
                log.Task = LogConstant.Task.Update;
                log.Description = "Finished: " + Finished + "; Finishdate: " + Finishdate + "; PartID: " + PartID + " where MONo: " + MONo;
                log.WriteLog();


            }
        }

        private void gridView1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Alt)
            {
                if (e.KeyCode == Keys.E)
                {
                    if (gridView1.OptionsBehavior.Editable)
                    {
                        //gridView1.fo;
                        gridView1.OptionsBehavior.Editable = false;
                    }
                    else
                    {
                        gridView1.OptionsBehavior.Editable = true;
                        gridView1.Columns["MONo"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["date"].OptionsColumn.AllowEdit = false;
                        //gridView1.Columns["PartID"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["MOQty"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["DeadLine"].OptionsColumn.AllowEdit = false;
                        //gridView1.Columns["HelisertQty"].OptionsColumn.AllowEdit = false;
                        //gridView1.Columns["BlastQty"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["Started"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["startdate"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["Imported"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["Note"].OptionsColumn.AllowEdit = false;
                    }
                }
            }
        }

    }
}
